4.1.FastAPI 通用模块

@app.get("/query_llm_user")
async def query_llm_user(username: str, session: AsyncSessionDep):
  result = await (await session.connection()).exec_driver_sql(
    "select * from llm_user where username = %s",
    tuple([username])
  )
  list = [dict(row._mapping) for row in result]
  return {"result": list[0]} if len(list) == 1 else {"result": None}

import os
import re

class GeneralUtils:

  @staticmethod
  def log_sql(sql, values):
    """打印sql执行日志，会将sql中的？占位符替换为对应顺序的参数值"""

    show_sql = os.getenv('GENERAL_SHOW_SQL', True)

    if show_sql:
      print("\n/*---------------------------------------log sql-------------------------------------------*/\n")
      print("-" * 20, "原始SQL", "-" * 20)
      print(sql)
      print("-" * 20, "SQL参数", "-" * 20)
      print(values)
      count = 0

      def replace_callback(match):
        nonlocal count
        val = values[count]
        count = count + 1
        if isinstance(val, str):
          return val
        if isinstance(val, list):
          return ', '.join(map(str, val))
        return str(val)

      target_sql = re.sub(r'\?+', replace_callback, sql)
      print("-" * 20, "目标SQL", "-" * 20)
      print(target_sql)
      print("\n")

  @staticmethod
  def get_value_sql(value, value_type, sql_values):
    """获取值sql，如果是字符串或者数字则直接返回占位符？，如果是日期时间则返回转化值占位字符串？"""
    if value_type == 'string' or value_type == 'number':
      sql_values.append(value)
      return '?'
    elif value_type == 'date':
      sql_values.append(value)
      return "str_to_date(?, '%%Y-%%m-%%d')"
    elif value_type == 'datetime':
      sql_values.append(value)
      return "str_to_date(?, '%%Y-%%m-%%d %%H:%%i:%%s')"
    elif value_type == 'time':
      sql_values.append(value)
      return "str_to_date(?, '%%H:%%i:%%s')"

  @staticmethod
  def get_value(obj, attr_name, default=None):
    """通用的获取属性值的方法"""
    if isinstance(obj, dict):
      return obj.get(attr_name, default)
    else:
      return getattr(obj, attr_name, default)

  @staticmethod
  def format_columns(columns):
    """
    根据字段信息生成两个字典，
    一个是通过驼峰命名找到字段对象的字典映射对象，
    另一个是通过下划线命名找到字段对象的字典映射对象
    """

    hump_to_columns = {}
    line_to_columns = {}

    for hump_name, col_config in columns.items():
      line_name = GeneralUtils.to_line(hump_name)
      query = GeneralUtils.get_value(col_config, "query", None) or f"t1.{line_name}"
      info = {
        **col_config,
        "hump_name": hump_name,
        "line_name": line_name,
        "query": query,
        "col_name": query.split('.')[1]
      }
      hump_to_columns[hump_name] = info
      line_to_columns[line_name] = info
    return {
      # 通过下划线命令查找列信息
      "hump_to_columns": hump_to_columns,
      # 通过驼峰命名查找列信息
      "line_to_columns": line_to_columns,
    }

  @staticmethod
  def to_line(hump_name: str) -> str:
    """驼峰命名转化成下划线命名"""
    return re.sub(r'([A-Z])', r'_\1', hump_name).lower()

import json

from app.general.sql_utils.GeneralUtils import GeneralUtils

class GeneralConvertor:
  def __init__(self, module_config):
    self.convert_columns = [
      (col_name, col_config)
      for col_name, col_config in module_config["columns"].items()
      if col_config.get("convert")
    ]

  def encode_list(self, arr):
    if not len(self.convert_columns):
      return
    for item in arr:
      for col_name, col_config in self.convert_columns:
        if GeneralUtils.get_value(item, col_name, None) is not None:
          if not isinstance(item[col_name], str):
            item[col_name] = ConvertTypes[col_config["convert"]]["encode"](item[col_name])

  def decode_list(self, arr):
    if not len(self.convert_columns):
      return
    for item in arr:
      for col_name, col_config in self.convert_columns:
        if GeneralUtils.get_value(item, col_name, None) is not None:
          if isinstance(item[col_name], str):
            item[col_name] = ConvertTypes[col_config["convert"]]["decode"](item[col_name])


MODULE_CONVERT_TYPE_ARRAY_JSON = 'arrayjson'
MODULE_CONVERT_TYPE_ARRAY_STRING = 'arraystring'


def array_json_encoder(val):
  return json.dumps(val)


def array_json_decoder(val):
  try:
    return json.loads(val)
  except json.JSONDecodeError:
    return ""


def array_string_encoder(val):
  return ",".join(val) if isinstance(val, list) else val


def array_string_decoder(val):
  return val.split(",") if isinstance(val, str) else val


ConvertTypes = {
  "arrayjson": {
    "encode": array_json_encoder,
    "decode": array_json_decoder,
  },
  "arraystring": {
    "encode": array_string_encoder,
    "decode": array_string_decoder,
  }
}

"arrayJson": {
  "valueType": "string",
  "convert": "arrayjson",
  "front": {
    "title": "Json数组",
    "type": "string"
  }
},
"arrayString": {
  "valueType": "string",
  "convert": "arraystring",
  "front": {
    "title": "String数组",
    "type": "string"
  }
},

from app.general.sql_utils.GeneralUtils import GeneralUtils


def build_insert_sql(module_config, row):
  column_info = GeneralUtils.format_columns(module_config["columns"])
  sqls = [f"insert into {GeneralUtils.get_value(module_config, 'tableName')}"]
  values = []

  field_sql_left_list = []
  field_sql_right_list = []
  field_sql_right_values = []

  for hump_name, column in column_info['hump_to_columns'].items():
    value = GeneralUtils.get_value(row, hump_name)

    # if (value === undefined) {return;}

    if value is None:
      continue;

    if 't1.' not in column['query']:
      continue

    field_sql_left_list.append(column['col_name'])
    field_sql_right_list.append(GeneralUtils.get_value_sql(
      value=value,
      value_type=column['valueType'],
      sql_values=field_sql_right_values,
    ))

  sqls.append(f"( {', '.join(field_sql_left_list)} ) ")
  sqls.append("values")
  sqls.append(f"( {', '.join(field_sql_right_list)} )")
  values.extend(field_sql_right_values)
  sql = ' '.join(sqls)
  GeneralUtils.log_sql(sql, values)
  sql = sql.replace("?", "%s")
  return (sql, values)


# build_insert_sql(DEMO_MODULE_CONFIG, {"normalText": "123", "numberVal": 213})

-------------------- 原始SQL --------------------
insert into pl_demo ( normal_text, number_val )  values ( ?, ? )
-------------------- SQL参数 --------------------
['123', 213]
-------------------- 目标SQL --------------------
insert into pl_demo ( normal_text, number_val )  values ( 123, 213 )

from datetime import datetime

from app.general.sql_utils.GeneralUtils import GeneralUtils

UPDATE_EXCLUDE_FIELDS = ['id', 'createAt', 'createdBy']

def build_update_sql(module_config, row, param_update_fields=None):
  row_id = GeneralUtils.get_value(row, 'id', None)

  if row_id is None:
    raise Exception("row_id is None")

  column_info = GeneralUtils.format_columns(module_config["columns"])
  sqls = [f"update {module_config['tableName']} set"]
  values = []

  field_sql_list = []
  for hump_name, column in column_info['hump_to_columns'].items():
    value = GeneralUtils.get_value(row, hump_name, None)
    if hump_name in UPDATE_EXCLUDE_FIELDS:
      continue
    if 't1.' not in column['query']:
      continue
    if param_update_fields is not None and hump_name not in param_update_fields:
      # 如果有指定更新的字段，并且humpName不在这个字段列表中，则不更新这个字段
      continue
    if hump_name == 'updateAt':
      value = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    field_sql_list.append(f"""{column['col_name']} = {GeneralUtils.get_value_sql(
      value=value,
      value_type=column['valueType'],
      sql_values=values,
    )}""")
  sqls.append(', '.join(field_sql_list))
  sqls.append("where id = ?")
  values.append(row_id)

  sql = ' '.join(sqls)
  GeneralUtils.log_sql(sql, values)
  sql = sql.replace("?", "%s")
  return (sql, values)


# build_update_sql(DEMO_MODULE_CONFIG, DEMO_QUERY_ROWS[0], ['normalText', 'numberVal'])

-------------------- 原始SQL --------------------
update pl_demo set normal_text = ?, number_val = ? where id = ?
-------------------- SQL参数 --------------------
['hello', 602, '8570453e-0c88-11ef-9e34-5254001d6dcd']
-------------------- 目标SQL --------------------
update pl_demo set normal_text = hello, number_val = 602 where id = 8570453e-0c88-11ef-9e34-5254001d6dcd

from app.general.sql_utils.GeneralUtils import GeneralUtils


def build_delete_sql(module_config, id):
  if isinstance(id, list):
    sql = f"delete from {module_config['tableName']} where id in ({','.join(['?'] * len(id))})"
    values = id

  else:
    sql = f"delete from {module_config['tableName']} where id = ?"
    values = [id]

  GeneralUtils.log_sql(sql, values)
  sql = sql.replace("?", "%s")
  return (sql, values)


# sql, values = build_delete_sql(DEMO_MODULE_CONFIG, ['111', '2222'])

-------------------- 原始SQL --------------------
delete from pl_demo where id in (?,?)
-------------------- SQL参数 --------------------
['111', '2222']
-------------------- 目标SQL --------------------
delete from pl_demo where id in (111,2222)

import re

from app.general.sql_utils.GeneralUtils import GeneralUtils


def build_query_sql(query_config, module_config):
  column_info = GeneralUtils.format_columns(module_config["columns"])

  # print("column_info ==>>", column_info)

  distinct_fields = GeneralUtils.get_value(query_config, 'distinctFields', [])

  has_distinct = len(distinct_fields) > 0

  # /*---------------------------------------field sql-------------------------------------------*/
  field_sql_list = []
  field_sql_values = []

  if has_distinct:
    print("has_distinct", distinct_fields)
    field_sql_list.append("distinct")
    distinct_field_strings = []
    for item_distinct_field in distinct_fields:
      item_column_info = GeneralUtils.get_value(GeneralUtils.get_value(column_info, 'humpToColumns'), item_distinct_field)
      item_field_string = f"{query_format(item_column_info['query'], item_column_info['valueType'])}"

      if not GeneralUtils.get_value(query_config, 'onlyCount', False):
        item_field_string = f"{item_field_string} as '{item_column_info['hump_name']}'"
      distinct_field_strings.append(item_field_string)
    field_sql_list.append(','.join(distinct_field_strings))
  else:
    field_strings = []
    for hump_name, _ in module_config['columns'].items():
      item_column_info = column_info["hump_to_columns"][hump_name]
      field_strings.append(f"{query_format(item_column_info['query'], item_column_info['valueType'])} as '{item_column_info['hump_name']}'")
    field_sql_list.append(','.join(field_strings))

  # print("field_sql_list ==>>", field_sql_list)
  # print("field_sql_values ==>>", field_sql_values)

  # /*---------------------------------------from sql-------------------------------------------*/

  from_sql_list = ['from']
  from_sql_values = []

  from_sql_list.append(f"{module_config['tableName']} t1")

  join_config = GeneralUtils.get_value(module_config, 'joinConfig', [])
  if len(join_config):
    for item_join_config in join_config:
      item_join_config_type = GeneralUtils.get_value(item_join_config, 'type', '')
      if item_join_config_type != 'right join' and item_join_config_type != 'left join' and item_join_config_type != 'join':
        raise ValueError(f"Can't recognise join type:{item_join_config_type}")
      on_left, on_right = item_join_config['on'].split('=')
      left_table, left_field = on_left.strip().split('.')
      right_table, right_field = on_right.strip().split('.')
      from_sql_list.append(f"{item_join_config_type} {item_join_config['table']} {item_join_config['alia']} on {left_table}.{left_field} = {right_table}.{right_field}")

  # /*---------------------------------------filter sql-------------------------------------------*/

  filter_sql_list = []
  filter_sql_values = []

  query_config_filters = GeneralUtils.get_value(query_config, 'filters', [])

  if len(query_config_filters):
    for index, item_filter in enumerate(query_config_filters):
      # print(index, item_filter)
      if GeneralUtils.get_value(item_filter, 'id', None) is None:
        item_filter['id'] = f"_{index}"
    # print(query_config_filters)
    filter_expression = GeneralUtils.get_value(query_config, 'filterExpression', None) or ' and '.join(item['id'] for item in query_config_filters)
    filter_expression = re.sub(r'\s+(并且|&&)\s+', ' and ', filter_expression)
    filter_expression = re.sub(r'\s+(或者|\|\|)\s+', ' or ', filter_expression)

    id_2_filter = {item['id']: item for item in query_config_filters}

    def replace_func(match):

      full_match = match.group(0)
      filter_id = full_match

      if filter_id == 'and' or filter_id == 'or':
        return filter_id

      filter_info = GeneralUtils.get_value(id_2_filter, filter_id, None)

      if filter_info is None:
        return f"[NoMatchFilterForId:{filter_id}]"

      filter_field = filter_info['field']
      item_column = GeneralUtils.get_value(column_info['hump_to_columns'], filter_field, None)

      if item_column is None:
        return f"[NoMatchColumnForField:{filter_field}]"

      filter_type = GeneralUtils.get_value(filter_info, 'type', None) or GeneralUtils.get_value(item_column, 'valueType', None) or 'string'
      value = GeneralUtils.get_value(filter_info, 'value', None)
      filter_operator = filter_info['operator']
      query = item_column['query']

      if filter_type == "string":
        if filter_operator == '=' or filter_operator == '>' or filter_operator == '>=' or filter_operator == '<' or filter_operator == '<=':
          filter_sql_values.append(value)
          return f"{query} = ?"
        elif filter_operator == '!=':
          filter_sql_values.append(value)
          return f"{query} != ?"
        elif filter_operator == '~':
          filter_sql_values.append(f"%{value}%")
          return f"{query} like ?"
        elif filter_operator == 'in':
          return format_in(value, query, False, filter_sql_values)
        elif filter_operator == 'not in':
          return format_in(value, query, True, filter_sql_values)
        elif filter_operator == 'in like':
          return format_in_like(value, query, False, filter_sql_values)
        elif filter_operator == 'not in like':
          return format_in_like(value, query, True, filter_sql_values)
        elif filter_operator == 'is null':
          return f"{query} is null"
        elif filter_operator == 'is not null':
          return f"{query} is not null"
      elif filter_type == "number":
        if filter_operator == '=':
          filter_sql_values.append(value)
          return f"{query} = ?"
        elif filter_operator == '!=':
          filter_sql_values.append(value)
          return f"{query} != ?"
        elif filter_operator == '~':
          filter_sql_values.append(f"%{value}%")
          return f"{query} like ?"
        elif filter_operator == '>':
          filter_sql_values.append(value)
          return f"{query} > ?"
        elif filter_operator == '>=':
          filter_sql_values.append(value)
          return f"{query} >= ?"
        elif filter_operator == '<':
          filter_sql_values.append(value)
          return f"{query} < ?"
        elif filter_operator == '<=':
          filter_sql_values.append(value)
          return f"{query} <= ?"
        elif filter_operator == 'in':
          return format_in(value, query, False, filter_sql_values)
        elif filter_operator == 'not in':
          return format_in(value, query, True, filter_sql_values)
        elif filter_operator == 'in like':
          return format_in_like(value, query, False, filter_sql_values)
        elif filter_operator == 'not in like':
          return format_in_like(value, query, True, filter_sql_values)
        elif filter_operator == 'is null':
          return f"{query} is null"
        elif filter_operator == 'is not null':
          return f"{query} is not null"
      elif filter_type == "date":
        if filter_operator == '=' or filter_operator == '~':
          filter_sql_values.append(value)
          return f"{date_format_sql(query)} = ?"
        elif filter_operator == '!=':
          filter_sql_values.append(value)
          return f"{date_format_sql(query)} != ?"
        elif filter_operator == '>':
          filter_sql_values.append(value)
          return f"{query} > ?"
        elif filter_operator == '>=':
          filter_sql_values.append(value)
          return f"{query} >= ?"
        elif filter_operator == '<':
          filter_sql_values.append(value)
          return f"{query} < ?"
        elif filter_operator == '<=':
          filter_sql_values.append(value)
          return f"{query} <= ?"
        elif filter_operator == 'in' or filter_operator == 'in like':
          v_list = format_string2array(value)
          filter_sql_values.extend(v_list)
          return f"{date_format_sql(query)} in ({','.join('?' for _ in v_list)})"
        elif filter_operator == 'not in' or filter_operator == 'not in like':
          v_list = format_string2array(value)
          filter_sql_values.extend(v_list)
          return f"{date_format_sql(query)} not in ({','.join('?' for _ in v_list)})"
        elif filter_operator == 'is null':
          return f"{query} is null"
        elif filter_operator == 'is not null':
          return f"{query} is not null"
      elif filter_type == "time":
        if filter_operator == '=' or filter_operator == '~':
          filter_sql_values.append(value)
          return f"{time_format_sql(query)} = ?"
        elif filter_operator == '!=':
          filter_sql_values.append(value)
          return f"{time_format_sql(query)} != ?"
        elif filter_operator == '>':
          filter_sql_values.append(value)
          return f"{query} > ?"
        elif filter_operator == '>=':
          filter_sql_values.append(value)
          return f"{query} >= ?"
        elif filter_operator == '<':
          filter_sql_values.append(value)
          return f"{query} < ?"
        elif filter_operator == '<=':
          filter_sql_values.append(value)
          return f"{query} <= ?"
        elif filter_operator == 'in' or filter_operator == 'in like':
          v_list = format_string2array(value)
          filter_sql_values.extend(v_list)
          return f"{time_format_sql(query)} in ({','.join('?' for _ in v_list)})"
        elif filter_operator == 'not in' or filter_operator == 'not in like':
          v_list = format_string2array(value)
          filter_sql_values.extend(v_list)
          return f"{time_format_sql(query)} not in ({','.join('?' for _ in v_list)})"
        elif filter_operator == 'is null':
          return f"{query} is null"
        elif filter_operator == 'is not null':
          return f"{query} is not null"
        return
      elif filter_type == "datetime":
        if filter_operator == '=' or filter_operator == '~':
          filter_sql_values.append(value)
          return f"{datetime_format_sql(query)} = ?"
        elif filter_operator == '!=':
          filter_sql_values.append(value)
          return f"{datetime_format_sql(query)} != ?"
        elif filter_operator == '>':
          filter_sql_values.append(value)
          return f"{query} > ?"
        elif filter_operator == '>=':
          filter_sql_values.append(value)
          return f"{query} >= ?"
        elif filter_operator == '<':
          filter_sql_values.append(value)
          return f"{query} < ?"
        elif filter_operator == '<=':
          filter_sql_values.append(value)
          return f"{query} <= ?"
        elif filter_operator == 'not in' or filter_operator == 'not in like':
          v_list = format_string2array(value)
          filter_sql_values.extend(v_list)
          return f"{datetime_format_sql(query)} in ({','.join('?' for _ in v_list)})"
        elif filter_operator == 'in' or filter_operator == 'in like':
          v_list = format_string2array(value)
          filter_sql_values.extend(v_list)
          return f"{datetime_format_sql(query)} not in ({','.join('?' for _ in v_list)})"
        elif filter_operator == 'is null':
          return f"{query} is null"
        elif filter_operator == 'is not null':
          return f"{query} is not null"
        return
      else:
        return f"NoMatchFilterType:{filter_type}"

      return f"filter type {filter_type} no match operator: ${filter_operator}"

    new_filter_expression = re.sub(r'[a-zA-Z0-9_-]+', replace_func, filter_expression)

    filter_sql_list.extend(['where', new_filter_expression])

  # /*---------------------------------------only count-------------------------------------------*/

  sqls = []
  values = []

  query_config_only_count = GeneralUtils.get_value(query_config, 'onlyCount', False)

  if query_config_only_count:
    if not has_distinct:
      sqls.append("select count(0) as total")
    else:
      sqls.append(f"select count( {' '.join(field_sql_list)} ) as total")
      values.extend(field_sql_values)

    sqls.extend(from_sql_list)
    values.extend(from_sql_values)

    sqls.extend(filter_sql_list)
    values.extend(filter_sql_values)
  else:
    sqls.append('select')

    sqls.extend(field_sql_list)
    values.extend(field_sql_values)

    sqls.extend(from_sql_list)
    values.extend(from_sql_values)

    sqls.extend(filter_sql_list)
    values.extend(filter_sql_values)

    def get_sort_sql_value():
      sort_sql_list = []
      sort_sql_values = []

      query_config_orders = GeneralUtils.get_value(query_config, 'orders', [])
      query_config_orders = query_config_orders if isinstance(query_config_orders, list) else [query_config_orders]

      if len(query_config_orders) > 0:
        sort_sql_list.append("order by")
        temp_list = []

        for sort_item in query_config_orders:
          sn = ''
          sc = ''

          if isinstance(sort_item, str):
            sn = sort_item
            sc = 'desc'
          else:
            sn = sort_item['field']
            sc = 'desc' if sort_item['desc'] else 'asc'

          column_item = GeneralUtils.get_value(column_info['hump_to_columns'], sn, None)

          if column_item is None:
            temp_list.append(f"[NoMatchSortField:{sn}]")
          else:
            temp_list.append(f"{column_item['query']} {sc}")

        sort_sql_list.append(', '.join(temp_list))
      return (sort_sql_list, sort_sql_values)

    sort_sql_list, sort_sql_values = get_sort_sql_value()

    sqls.extend(sort_sql_list)
    values.extend(sort_sql_values)

  query_config_all = GeneralUtils.get_value(query_config, 'all', False)

  if not query_config_all:
    sqls.append("limit ?,?")
    values.extend([
      GeneralUtils.get_value(query_config, 'offset', 0),
      GeneralUtils.get_value(query_config, 'size', 10)
    ])
  # /*---------------------------------------end-------------------------------------------*/

  sql = ' '.join(sqls)
  GeneralUtils.log_sql(sql, values)
  sql = sql.replace("?", "%s")

  return (sql, values)


def query_format(query: str, value_type: str):
  if value_type == "string" or value_type == "number":
    return query
  elif value_type == "date":
    return date_format_sql(query)
  elif value_type == "datetime":
    return datetime_format_sql(query)
  elif value_type == "time":
    return time_format_sql(query)


def date_format_sql(query: str):
  return f"date_format({query}, '%%Y-%%m-%%d')"


def datetime_format_sql(query: str):
  return f"date_format({query}, '%%Y-%%m-%%d %%H:%%i:%%s')"


def time_format_sql(query: str):
  return f"date_format({query}, '%%H:%%i:%%s')"


def format_in(value, query, not_in, value_list):
  # value_list.append(query)
  # 如果 value 不是列表，将其按逗号分割成列表
  if not isinstance(value, list):
    list_ = value.split(',')
  else:
    list_ = value

  # 将 list_ 中的元素添加到 valueList 中
  value_list.extend(list_)

  # 生成格式化字符串
  result = f"{query} {'not ' if not_in else ''}in ({','.join('?' for _ in list_)})"
  return result


def format_in_like(value, query, not_like, value_list):
  # 如果 value 不是列表，将其按逗号分割成列表
  if not isinstance(value, list):
    list_ = value.split(',')
  else:
    list_ = value

  if not_like:
    result_list = []
    for item in list_:
      value_list.append(f"%{item}%")
      result_list.append(f"{query} not like ?")
    return f"({' and '.join(result_list)})"
  else:
    result_list = []
    for item in list_:
      value_list.append(f"%{item}%")
      result_list.append(f"{query} like ?")
    return f"({' or '.join(result_list)})"


def format_string2array(val):
  return val if isinstance(val, list) else val.split(',')


# /*---------------------------------------查询sql生成测试-------------------------------------------*/

# sql, values = build_query_sql({
#   "page": 0,
#   "size": 10,
#   "filters": [
#     {
#       "field": "count",
#       "operator": "is null",
#       "id": "query_meta_1"
#     }
#   ]
# }, DEMO_MODULE_CONFIG)

-------------------- 原始SQL --------------------
select t1.id as 'id',date_format(t1.created_at, '%%Y-%%m-%%d %%H:%%i:%%s') as 'createdAt',t1.created_by as 'createdBy',date_format(t1.updated_at, '%%Y-%%m-%%d %%H:%%i:%%s') as 'updatedAt',t1.updated_by as 'updatedBy',t1.count as 'count',t1.normal_text as 'normalText',t1.long_text as 'longText',t1.number_val as 'numberVal',t1.flag as 'flag',t1.select_val as 'selectVal',t1.color_val as 'colorVal',date_format(t1.date_val, '%%Y-%%m-%%d') as 'dateVal',date_format(t1.time_val, '%%H:%%i:%%s') as 'timeVal',t1.parent_id as 'parentId',t1.image_id as 'imageId',t2.normal_text as 'parentName',t1.province_val as 'provinceVal',t1.city_val as 'cityVal',t1.district_val as 'districtVal',t1.ov_val as 'ovVal',t1.array_json as 'arrayJson',t1.array_string as 'arrayString',t1.array_json_str as 'arrayJsonStr',t1.array_string_str as 'arrayStringStr' from pl_demo t1 left join pl_demo t2 on t1.parent_id = t2.id where t1.count is null limit ?,?
-------------------- SQL参数 --------------------
[0, 10]
-------------------- 目标SQL --------------------
select t1.id as 'id',date_format(t1.created_at, '%%Y-%%m-%%d %%H:%%i:%%s') as 'createdAt',t1.created_by as 'createdBy',date_format(t1.updated_at, '%%Y-%%m-%%d %%H:%%i:%%s') as 'updatedAt',t1.updated_by as 'updatedBy',t1.count as 'count',t1.normal_text as 'normalText',t1.long_text as 'longText',t1.number_val as 'numberVal',t1.flag as 'flag',t1.select_val as 'selectVal',t1.color_val as 'colorVal',date_format(t1.date_val, '%%Y-%%m-%%d') as 'dateVal',date_format(t1.time_val, '%%H:%%i:%%s') as 'timeVal',t1.parent_id as 'parentId',t1.image_id as 'imageId',t2.normal_text as 'parentName',t1.province_val as 'provinceVal',t1.city_val as 'cityVal',t1.district_val as 'districtVal',t1.ov_val as 'ovVal',t1.array_json as 'arrayJson',t1.array_string as 'arrayString',t1.array_json_str as 'arrayJsonStr',t1.array_string_str as 'arrayStringStr' from pl_demo t1 left join pl_demo t2 on t1.parent_id = t2.id where t1.count is null limit 0,10

from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import AsyncConnection

from app.general.sql_utils.GeneralConvertor import GeneralConvertor
from app.general.sql_utils.GeneralUtils import GeneralUtils
from app.general.sql_utils.build_delete_sql import build_delete_sql
from app.general.sql_utils.build_insert_sql import build_insert_sql
from app.general.sql_utils.build_query_sql import build_query_sql
from app.general.sql_utils.build_update_sql import build_update_sql
from app.utils.next_id import next_id


class GeneralService:
  def __init__(self, module_config):
    self.module_config = module_config
    self.convertor = GeneralConvertor(module_config)

  async def select(self, conn: AsyncConnection, query_config, debug_data=[]):

    n_page = GeneralUtils.get_value(query_config, 'page', 0)
    n_size = GeneralUtils.get_value(query_config, 'size', 5)
    n_only_count = GeneralUtils.get_value(query_config, 'onlyCount', False)

    offset = n_page * n_size
    # 多查一条数据，方便判断是否有下一页数据
    size = n_size + 1

    target_query_config = {
      **query_config,
      "offset": offset,
      "size": size,
      "orders": get_default_orders(query_config, self.module_config)
    }

    sql, values = build_query_sql(target_query_config, self.module_config)

    try:

      debug_data.append({"sql": sql, "values": values})
      result = await conn.exec_driver_sql(sql, tuple(values))
      result = [dict(row._mapping) for row in result]

      self.convertor.decode_list(result)

      if n_only_count:
        return {"total": result[0]['total']}
      else:
        has_next = False if GeneralUtils.get_value(query_config, 'all', False) else len(result) == n_size + 1
        if has_next:
          result.pop()
        return {
          "hasNext": has_next,
          "list": result,
        }
    except SQLAlchemyError as err:
      return {"error": f"Error: {err}", }

  async def select_one(self, conn: AsyncConnection, query_config, debug_data=[]):
    target_query_config = {
      "offset": 0,
      "size": 1,
      "filters": [],
      "orders": {"field": "createdAt", "desc": True},
    }
    for humpName, value in query_config.items():
      target_query_config['filters'].append({
        "field": humpName,
        "value": value,
        "operator": "="
      })

    result = await self.select(conn=conn, query_config=target_query_config, debug_data=debug_data)

    if "error" in result:
      return result

    return {"result": None if "list" not in result or len(result['list']) == 0 else result['list'][0]}

  async def insert(self, conn: AsyncConnection, query_config, debug_data=[]):

    row = GeneralUtils.get_value(query_config, 'row', None)
    if row is None:
      return {
        "error": "row parameter is missing",
      }
    self.convertor.encode_list([row])

    row_id = GeneralUtils.get_value(row, 'id', None)

    if row_id is None:
      row_id = await next_id(1)
      row['id'] = row_id

    try:
      sql, values = build_insert_sql(self.module_config, row)

      debug_data.append({"sql": sql, "values": values})
      await conn.exec_driver_sql(sql, tuple(values))
      await conn.commit()

      sql, values = build_query_sql({"page": 0, "size": 1, "filters": [{"field": "id", "operator": "=", "value": row_id}]}, self.module_config)
      debug_data.append({"sql": sql, "values": values})
      result = await conn.exec_driver_sql(sql, tuple(values))
      result = [dict(row._mapping) for row in result]

      self.convertor.decode_list(result)

      if len(result) > 0:
        return {"result": result[0]}
      else:
        return {
          "error": "insert failed, query result is empty",
        }

    except SQLAlchemyError as err:
      return {"error": f"Error: {err}", }

  async def batch_insert(self, conn: AsyncConnection, query_config, debug_data=[]):

    rows = GeneralUtils.get_value(query_config, 'rows', None)
    if rows is None or len(rows) == 0:
      return {"error": "rows parameter is missing", }
    self.convertor.encode_list(rows)

    row_id_list = []
    for row in rows:
      row_id = GeneralUtils.get_value(row, 'id', None)
      if row_id is None:
        row_id = await next_id(1)
        row['id'] = row_id
      row_id_list.append(row_id)

    try:
      for row in rows:
        sql, values = build_insert_sql(self.module_config, row)
        debug_data.append({"sql": sql, "values": values})
        await conn.exec_driver_sql(sql, tuple(values))

      await conn.commit()

      sql, values = build_query_sql({"all": True, "filters": [{"field": "id", "operator": "in", "value": row_id_list}]}, self.module_config)
      debug_data.append({"sql": sql, "values": values})
      result = await conn.exec_driver_sql(sql, tuple(values))
      result = [dict(row._mapping) for row in result]

      self.convertor.decode_list(result)

      if len(result) > 0:
        return {"result": result}
      else:
        return {"error": "insert failed, query result is empty", }

    except SQLAlchemyError as err:
      return {"error": f"Error: {err}", }

  async def update(self, conn: AsyncConnection, query_config, debug_data=[]):

    row = GeneralUtils.get_value(query_config, 'row', None)
    update_fields = GeneralUtils.get_value(query_config, 'updateFields', None)

    if row is None:
      return {"error": "row parameter is missing", }

    row_id = GeneralUtils.get_value(row, 'id', None)

    if row_id is None:
      return {"error": "row is missing field: id", }

    self.convertor.encode_list([row])

    try:
      sql, values = build_update_sql(self.module_config, row, update_fields)

      debug_data.append({"sql": sql, "values": values})
      await conn.exec_driver_sql(sql, tuple(values))
      await conn.commit()

      sql, values = build_query_sql({"page": 0, "size": 1, "filters": [{"field": "id", "operator": "=", "value": row_id}]}, self.module_config)
      debug_data.append({"sql": sql, "values": values})
      result = await conn.exec_driver_sql(sql, tuple(values))
      result = [dict(row._mapping) for row in result]

      self.convertor.decode_list(result)

      if len(result) > 0:
        return {"result": result[0]}
      else:
        return {"error": "update failed, query result is empty", }

    except SQLAlchemyError as err:
      return {"error": f"Error: {err}", }

  async def batch_update(self, conn: AsyncConnection, query_config, debug_data=[]):

    rows = GeneralUtils.get_value(query_config, 'rows', None)
    update_fields = GeneralUtils.get_value(query_config, 'updateFields', None)

    if rows is None or len(rows) == 0:
      return {"error": "rows parameter is missing", }
    self.convertor.encode_list(rows)

    row_id_list = []
    for row in rows:
      row_id = GeneralUtils.get_value(row, 'id', None)
      if row_id is None:
        return {
          "error": "row is missing field: id",
          "row": row,
        }
      row_id_list.append(row_id)

    try:
      for row in rows:
        sql, values = build_update_sql(self.module_config, row, update_fields)
        debug_data.append({"sql": sql, "values": values})
        await conn.exec_driver_sql(sql, tuple(values))

      await conn.commit()

      sql, values = build_query_sql({"all": True, "filters": [{"field": "id", "operator": "in", "value": row_id_list}]}, self.module_config)
      debug_data.append({"sql": sql, "values": values})
      result = await conn.exec_driver_sql(sql, tuple(values))
      result = [dict(row._mapping) for row in result]

      self.convertor.decode_list(result)

      if len(result) > 0:
        return {"result": result}
      else:
        return {"error": "update failed, query result is empty", }

    except SQLAlchemyError as err:
      return {"error": f"Error: {err}", }

  async def delete(self, conn: AsyncConnection, query_config, debug_data=[]):

    id = GeneralUtils.get_value(query_config, 'id', None)

    if id is None:
      return {"error": "id parameter is missing", }

    try:
      sql, values = build_delete_sql(self.module_config, id)
      debug_data.append({"sql": sql, "values": values})
      result = await conn.exec_driver_sql(sql, tuple(values))
      await conn.commit()

      deleted_rows = result.rowcount
      if deleted_rows >= 1:
        return {"deletedRows": deleted_rows}
      else:
        return {"error": f"delete failed, delete rows is {deleted_rows}", }
    except SQLAlchemyError as err:
      return {"error": f"Error: {err}", }


def get_default_orders(query_config, module_config):
  orders = GeneralUtils.get_value(query_config, 'orders', None)
  if orders is not None:
    return orders
  module_config_default_orders = GeneralUtils.get_value(
    GeneralUtils.get_value(module_config, 'default', {}),
    'orders',
    None
  )
  if module_config_default_orders is not None:
    return module_config_default_orders
  return {"field": "createdAt", "desc": True}

import json
from typing import Literal

from sqlalchemy.ext.asyncio import AsyncConnection

from app.general.module_configs.default_module_configs import DefaultModuleConfigs
from app.general.sql_utils.GeneralService import GeneralService
from app.general.sql_utils.MODULE_CONFIG_MODULE import ModuleConfigsModule


async def perform_general_operation(
  conn: AsyncConnection,
  module: str,
  data: dict,
  debugData=None,
  type: Literal['list', 'item', 'insert', 'update', 'batchInsert', 'batchUpdate', 'delete'] = 'query'
):
  if debugData is None:
    debugData = []

  module_config = DefaultModuleConfigs.get(module, None)

  if module_config is None:

    module_query_result = await get_module_config(conn, module, debugData)

    if 'error' in module_query_result:
      return module_query_result

    module_config = module_query_result.get('result', {"": ""}).get('moduleConfig', None)

    if module_config is not None:
      module_config = json.loads(module_config)

  if module_config is None:
    return {
      "error": f"Can't find module config match: {module}",
      "body": module_query_result,
    }
  service = GeneralService(module_config=module_config)

  if type == 'list':
    return await service.select(conn, data, debugData)
  elif type == 'item':
    return await service.select_one(conn, data, debugData)
  elif type == 'insert':
    return await service.insert(conn, data, debugData)
  elif type == 'update':
    return await service.update(conn, data, debugData)
  elif type == 'batchInsert':
    return await service.batch_insert(conn, data, debugData)
  elif type == 'batchUpdate':
    return await service.batch_update(conn, data, debugData)
  elif type == 'delete':
    return await service.delete(conn, data, debugData)
  else:
    return {"error": f"Can't handle operation type: {type}"}


async def get_module_config(conn: AsyncConnection, module: str, debugData=None):
  service = GeneralService(module_config=ModuleConfigsModule)
  return await service.select_one(conn, {"code": module}, debugData)

from fastapi import FastAPI
from starlette.responses import JSONResponse

from app.general.perform_general_operation import perform_general_operation
from app.utils.db_utils import AsyncSessionDep


def add_generate_route(
  app: FastAPI,
):
  types = ['list', 'item', 'insert', 'update', 'batchInsert', 'batchUpdate', 'delete']
  for type in types:
    @app.post("/{module}/" + type)
    async def func(module: str, query_param: dict, session: AsyncSessionDep, type=type):
      conn = await session.connection()
      try:
        result = await perform_general_operation(conn=conn, module=module, data=query_param, debugData=[], type=type)
        if "error" in result:
          return JSONResponse(content=result, status_code=500)
        else:
          return result
      finally:
        if conn:
          await conn.close()

  @app.post("/{module}/" + 'remove')
  async def func(module: str, query_param: dict, session: AsyncSessionDep, type='delete'):
    conn = await session.connection()
    try:
      result = await perform_general_operation(conn=conn, module=module, data=query_param, debugData=[], type=type)
      if "error" in result:
        return JSONResponse(content=result, status_code=500)
      else:
        return result
    finally:
      if conn:
        await conn.close()

  @app.post("/{module}/" + 'batch')
  async def func(module: str, query_param: dict, session: AsyncSessionDep, type='batchInsert'):
    conn = await session.connection()
    try:
      result = await perform_general_operation(conn=conn, module=module, data=query_param, debugData=[], type=type)
      if "error" in result:
        return JSONResponse(content=result, status_code=500)
      else:
        return result
    finally:
      if conn:
        await conn.close()

  @app.put("/{module}/" + 'batch')
  async def func(module: str, query_param: dict, session: AsyncSessionDep, type='batchUpdate'):
    conn = await session.connection()
    try:
      result = await perform_general_operation(conn=conn, module=module, data=query_param, debugData=[], type=type)
      if "error" in result:
        return JSONResponse(content=result, status_code=500)
      else:
        return result
    finally:
      if conn:
        await conn.close()

add_generate_route(app)

DEMO_MODULE_CONFIG = {
  "tableName": "pl_demo",
  "base": "demo",
  "columns": {
    "id": {"valueType": "string"},
    "createdAt": {"valueType": "datetime"},
    "createdBy": {"valueType": "string"},
    "updatedAt": {"valueType": "datetime"},
    "updatedBy": {"valueType": "string"},
    "count": {"valueType": "number"},
    "normalText": {"valueType": "string"},
    "longText": {"valueType": "string"},
    "numberVal": {"valueType": "number"},
    "flag": {"valueType": "string"},
    "selectVal": {"valueType": "string"},
    "colorVal": {"valueType": "string"},
    "dateVal": {"valueType": "date"},
    "timeVal": {"valueType": "time"},
    "parentId": {"valueType": "string"},
    "imageId": {"valueType": "string"},
    "parentName": {"valueType": "string", "query": "t2.normal_text"},
    "provinceVal": {"valueType": "string"},
    "cityVal": {"valueType": "string"},
    "districtVal": {"valueType": "string"},
    "ovVal": {"valueType": "string"},
    "arrayJson": {"valueType": "string", "convert": "arrayjson"},
    "arrayString": {"valueType": "string", "convert": "arraystring"},
    "arrayJsonStr": {"valueType": "string"},
    "arrayStringStr": {"valueType": "string"}
  },
  "joinConfig": [
    {
      "type": "left join",
      "table": "pl_demo",
      "alia": "t2",
      "on": "t1.parent_id = t2.id"
    }
  ]
}

DEMO_LLM_USER_CONFIG = {
  "tableName": 'llm_user',
  "base": '/llm_user',
  "columns": {
    "id": {"valueType": "string"},
    "createdAt": {"valueType": "datetime"},
    "createdBy": {"valueType": "string"},
    "updatedAt": {"valueType": "datetime"},
    "updatedBy": {"valueType": "string"},
    "fullName": {"valueType": "string"},
    "username": {"valueType": "string"},
    "password": {"valueType": "number"},
    "memberStart": {"valueType": "string"},
    "memberEnd": {"valueType": "string"},
  },
}

DEMO_QUERY_ROWS = [
  {
    "id": "8570453e-0c88-11ef-9e34-5254001d6dcd",
    "createdAt": "2024-05-07 23:43:16",
    "createdBy": None,
    "updatedAt": "2024-10-31 14:41:28",
    "updatedBy": None,
    "count": 409,
    "normalText": "hello",
    "longText": None,
    "numberVal": 602,
    "flag": None,
    "selectVal": "consumer",
    "colorVal": None,
    "dateVal": "2024-05-14",
    "timeVal": None,
    "parentId": "9075e7c9-a9db-11ed-8add-525400138871",
    "imageId": None,
    "parentName": "Michael",
    "provinceVal": "310000",
    "cityVal": "310100",
    "districtVal": "310101",
    "ovVal": "member_discount",
    "arrayJson": [
      "consumer",
      "potential"
    ],
    "arrayString": [
      "consumer",
      "store"
    ],
    "arrayJsonStr": "[\"consumer\",\"potential\"]",
    "arrayStringStr": "consumer,potential"
  },
  {
    "id": "134437d3-fed2-11ee-9e34-5254001d6dcd",
    "createdAt": "2024-04-20 12:54:31",
    "createdBy": None,
    "updatedAt": "2024-10-31 14:10:46",
    "updatedBy": None,
    "count": 347,
    "normalText": "session",
    "longText": None,
    "numberVal": 456,
    "flag": None,
    "selectVal": "potential",
    "colorVal": None,
    "dateVal": "2024-04-03",
    "timeVal": None,
    "parentId": "90bf805f-a9db-11ed-8add-525400138871",
    "imageId": None,
    "parentName": "Michael3",
    "provinceVal": "150000",
    "cityVal": "150500",
    "districtVal": "150523",
    "ovVal": "member_discount",
    "arrayJson": [
      "consumer"
    ],
    "arrayString": [
      "consumer"
    ],
    "arrayJsonStr": "[\"consumer\",\"store\"]",
    "arrayStringStr": "consumer,store"
  }
]

DEMO_QUERY_CONFIG = {
  "offset": 0,
  "size": 8,
  "filters": [
    {
      "field": "normalText",
      "value": "ro",
      "operator": "~",
      "id": "query_meta_1"
    },
    {
      "field": "numberVal",
      "value": 100,
      "operator": ">",
    }
  ],
  "orders": [
    {
      "field": "createdAt",
      "desc": True
    },
    {
      "field": "updatedAt",
      "desc": False
    }
  ]
}

ModuleConfigsModule = {
  "tableName": 'pl_module',
  "base": '/module',
  "columns": {
    "id": {
      "valueType": 'string',
      "front": {
        "title": '编号',
      },
    },
    "createdAt": {
      "valueType": 'datetime',
      "front": {
        "title": '创建时间',
      },
    },
    "createdBy": {
      "valueType": 'string',
      "front": {
        "title": '创建人',
      },
    },
    "updatedAt": {
      "valueType": 'datetime',
      "front": {
        "title": '更新时间',
      },
    },
    "updatedBy": {
      "valueType": 'string',
      "front": {
        "title": '更新人',
      },
    },
    "label": {
      "valueType": 'string',
      "front": {
        "title": '模块名称',
      },
    },
    "code": {
      "valueType": 'string',
      "front": {
        "title": '模块标识',
      },
    },
    "remarks": {
      "valueType": 'string',
      "front": {
        "title": '备注',
      },
    },
    "moduleConfig": {
      "valueType": 'string',
      "front": {
        "title": '模块配置信息',
      },
    },
  },
}

from app.general.module_configs.module_configs_address import ModuleConfigsAddress
from app.general.module_configs.module_configs_file import ModuleConfigsFile
from app.general.module_configs.module_configs_ov import ModuleConfigsOv
from app.general.sql_utils.MODULE_CONFIG_MODULE import ModuleConfigsModule

# 在查找moduleConfig的时候，优先从这个DefaultModuleConfigs中查找，减少请求数据库的次数
DefaultModuleConfigs = {
  "ov": ModuleConfigsOv,
  "file": ModuleConfigsFile,
  "upload": ModuleConfigsFile,
  "module": ModuleConfigsModule,
  "address": ModuleConfigsAddress,
}

ModuleConfigsAddress = {
  "tableName": 'pl_address',
  "base": '/address',
  "columns": {
    "id": {
      "valueType": 'string',
      "front": {
        "title": '编号',
      },
    },
    "createdAt": {
      "valueType": 'datetime',
      "front": {
        "title": '创建时间',
      },
    },
    "createdBy": {
      "valueType": 'string',
      "front": {
        "title": '创建人',
      },
    },
    "updatedAt": {
      "valueType": 'datetime',
      "front": {
        "title": '更新时间',
      },
    },
    "updatedBy": {
      "valueType": 'string',
      "front": {
        "title": '更新人',
      },
    },
    "name": {
      "valueType": 'string',
      "front": {
        "title": '地址名称',
      },
    },
    "code": {
      "valueType": 'string',
      "front": {
        "title": '地址编码',
      },
    },
    "deep": {
      "valueType": 'number',
      "front": {
        "title": '地址层级',
      },
    },
    "parentCode": {
      "valueType": 'string',
      "front": {
        "title": '父地址编码',
      },
    },
    "longitude": {
      "valueType": 'string',
      "front": {
        "title": '经度',
      },
    },
    "latitude": {
      "valueType": 'string',
      "front": {
        "title": '纬度',
      },
    },
    "parentName": {
      "front": {
        "title": '父地址名称',
      },
      "valueType": 'string',
      "query": 't2.name',
    },
  },
  "joinConfig": [
    {
      "type": 'left join',
      "table": 'pl_address',
      "alia": 't2',
      "on": 't1.parent_code = t2.code',
    },
  ],
}

ModuleConfigsFile = {
  "tableName": 'pl_upload',
  "base": '/upload',
  "columns": {
    "id": {
      "valueType": 'string',
      "front": {
        "title": '编号',
      },
    },
    "createdAt": {
      "valueType": 'datetime',
      "front": {
        "title": '创建时间',
      },
    },
    "createdBy": {
      "valueType": 'string',
      "front": {
        "title": '创建人',
      },
    },
    "updatedAt": {
      "valueType": 'datetime',
      "front": {
        "title": '更新时间',
      },
    },
    "updatedBy": {
      "valueType": 'string',
      "front": {
        "title": '更新人',
      },
    },
    "name": {
      "valueType": 'string',
      "front": {
        "title": '文件名',
      },
    },
    "path": {
      "valueType": 'string',
      "front": {
        "title": '文件路径',
      },
    },
    "headId": {
      "valueType": 'string',
      "front": {
        "title": '文件所属对象ID',
      },
    },
    "parentId": {
      "valueType": 'string',
      "front": {
        "title": '文件的父ID',
      },
    },
    "attr1": {
      "valueType": 'string',
      "front": {
        "title": '扩展属性1',
      },
    },
    "attr2": {
      "valueType": 'string',
      "front": {
        "title": '扩展属性2',
      },
    },
    "attr3": {
      "valueType": 'string',
      "front": {
        "title": '扩展属性3',
      },
    },
  },
}